const page = 1
const pageSize = 10
const carNumber = ''
const personName = '0'
const cardStatus = 1

// 查找全部参数
let query = `SELECT * FROM monthly_card`
// 如果有查询参数进来就不使用分页功能
if (carNumber || personName || (cardStatus === 0 || cardStatus === 1)) {
  query += ' WHERE '
  //    判断三个都有值的情况
  if (carNumber && personName && (cardStatus === 0 || cardStatus === 1)) {
    query += `carNumber LIKE ${carNumber} AND personName LIKE ${personName} AND cardStatus LIKE ${cardStatus}`
  } else if (carNumber && personName) {
    query += `carNumber LIKE ${carNumber} AND personName LIKE ${personName}`
  } else if (carNumber && (cardStatus === 0 || cardStatus === 1)) {
    query += `carNumber LIKE ${carNumber} AND cardStatus LIKE ${cardStatus}`
  } else if (personName && (cardStatus === 0 || cardStatus === 1)) {
    query += `personName LIKE ${personName} AND cardStatus LIKE ${cardStatus}`
  } else if (carNumber) query += `carNumber LIKE ${carNumber}`
  else if (personName) query += `personName LIKE ${personName}`
  else if ((cardStatus === 0 || cardStatus === 1)) query += `cardStatus LIKE ${cardStatus}`

  getMontylyCard(query, 666)
} else {
// 如果没有查询参数就可以使用分页功能
  connection.query('SELECT COUNT(*) as total FROM monthly_card', (err, countResult) => {
    if (err) {
      return res.status(500).json({ code: 500, msg: '服务器错误' })
    }
    //        数据库的总条数
    const count = countResult[0].total
    let startPage = page
    let endPage = pageSize
    if (page === 1 && pageSize < count) {
      startPage = 0
      endPage = pageSize
    } else if ((page * pageSize) < count) {
      startPage = (page - 1) * pageSize
      endPage = pageSize
    } else if ((page * pageSize) >= count) {
      startPage = (page - 1) * pageSize
      endPage = count
    } else if (pageSize > count) {
      startPage = 0
      endPage = count
    }
  })
  // 分页数据判断
  const pageQuery = `SELECT * FROM monthly_card ORDER BY id  LIMIT ${startPage},${endPage}`
  getMontylyCard(pageQuery, total)
}
function getMontylyCard(query, total) {
  connection.query(pageQuery, (err, results) => {
    if (err || results.length === 0) {
      return res.status(405).json({ code: 405, msg: '请求错误' })
    }
    //            console.log(results)
    res.status(200).send({
      code: 1000,
      msg: '获取信息成功',
      data: {
        total: total,
        rows: results.map(row => ({
          id: row.id,
          personName: row.personName,
          phoneNumber: row.phoneNumber,
          carNumber: row.carNumber,
          carBrand: row.carBrand,
          cardStatus: row.cardStatus,
          totalEffectiveDate: row.totalEffectiveDate
        }))
      }
    })
  })
}
